# 空运 需要找pdf文件
import json
import os
import xlwings as xw
import polars as pl
import win32com.client as win32

import util
from util.common import create_zip_file, create_folder_if_not_exists


def create_email_template(recipient_email, box_html, bf_html, mailAttachments, excel_file_path, subject):
    outlook = win32.Dispatch('Outlook.Application')
    # 创建一封邮件
    mail = outlook.CreateItem(0)

    # 邮件主题
    mail.Subject = subject
    # if recipients_to==None:
    #     recipients_to = ''

    # 邮件收件人
    mail.To = recipient_email if recipient_email else ''
    head = """
        <head>
        <style>
        body {
                font-size: 14px;     /* 设置字体大小为5号（14px） */
                font-family: 'SimSun', 'Microsoft YaHei', sans-serif;  /* 优先使用宋体 */
                mso-font-alt: 'SimSun'; /* 强制指定宋体 */
            }
            table {
                    border-collapse: collapse;
                }
            th, td {
                text-align: center;  /* 将文本居中 */
                padding: 8px;        /* 添加一些内边距以改善可读性 */

            }
            th {
                background: #86A8E7;
            }
            td {
                line-height: 7px;    /* 设置行高为10像素 */
                height: 7px;         /* 设置单元格高度为10像素 */
            }
            .last-row {
                font-weight: bold;
                color: #0000DE;
            }
        </style>
        </head>
        """
    # 获取当前邮件的签名（如果有）
    mail.Display()  # 显示邮件窗口，Outlook会自动加载签名
    signature = mail.HTMLBody  # 获取包含签名的当前HTMLBody
    # 邮件正文（文本）
    if bf_html != '':
        custom_body = f"""
        <html>
        {head}
        <body>
        <br />
        {box_html}
         <h3>附电池清单：</h3>
        {bf_html}
        </body>
        </html>
        """
    else:
        custom_body = f"""
                <html>
                {head}
                <body>
                <br />
                {box_html}
                </body>
                </html>
                """
    # 将自定义内容添加到签名前
    mail.HTMLBody = custom_body + signature
    mail.Attachments.Add(util.png_path) # 添加图片
    MAX_ATTACHMENT_SIZE = util.MaximumAttachmentSize * 1024 * 1024  # 10MB
    sumsize = 0
    for att in mailAttachments:
        # 累加附件大小
        sumsize += os.path.getsize(att)
    sumsize += os.path.getsize(excel_file_path)
    if sumsize < MAX_ATTACHMENT_SIZE:
        # 添加附件（确保路径正确）
        for att in mailAttachments:
            mail.Attachments.Add(str(att))
        mail.Attachments.Add(excel_file_path)
    else:
        mail.Subject = f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件"
    # 显示邮件前预览
    mail.Display()


def create_oneEmail(item_df, excel_file_path, box_df, subject):
    try:
        item_list = item_df['item'].to_list()
        df_CertificateCode = pl.DataFrame()
        if len(item_list) > 0:
            items1 = []
            for item in item_list:
                if item not in items1:
                    items1.append(item)
            items = ''
            for i in items1:
                items += f"'{i}',"
            items = items[:-1]
            # data_sql = f"SELECT Item, CertificateCode FROM CertificateCode WHERE Item in ({items})"
            data_sql = f"""
                        SELECT prio, Item, CertificateCode
                        FROM CertificateCode
                        WHERE (Item, prio) IN (
                            SELECT Item, MIN(prio)
                            FROM CertificateCode
                            WHERE Item IN ({items})
                            GROUP BY Item
                        )
                    """

            df_CertificateCode = (
                util.sqlite.query(data_sql)
                .group_by('Item').agg(pl.col('CertificateCode').str.concat(','))
            )
        if df_CertificateCode.height > 0:
            item_df = (
                item_df.join(df_CertificateCode, left_on='item', right_on='Item', how='left')
                .select(pl.all().fill_null(''))
            )
        else:
            item_df = item_df.with_columns(pl.lit('').alias('CertificateCode'))

        if util.pdf_path == '':
            return "没有设置pdf文件存放路径，请检查配置文件"

        tr = ''
        total_rows = box_df.shape[0]
        for i, r in enumerate(box_df.iter_rows(), start=1):
            if i < total_rows:
                tr += f"""
                                <tr>
                                <td>{r[0]}</td>
                                <td>{r[1]}</td>
                                <td>{r[2]}</td>
                                <td>{r[3]}</td>
                                <td>{r[4]}</td>
                                <td>{r[5]}</td>
                                <td>{r[6]}</td>
                                <td>{r[7]}</td>
                            </tr>
                                """
            else:
                tr += f"""
                                <tr class="last-row">
                                <td>{r[0]}</td>
                                <td>{r[1]}</td>
                                <td>{r[2]}</td>
                                <td>{r[3]}</td>
                                <td>{r[4]}</td>
                                <td>{r[5]}</td>
                                <td>{r[6]}</td>
                                <td>{r[7]}</td>
                            </tr>
                                """
        # html模板
        box_html = f"""
                        <table  border=1>
                            <thead>
                                <tr>
                                    <th>箱单号</th>
                                    <th>合同号</th>
                                    <th>贸易术语</th>
                                    <th>危险品标识</th>
                                    <th>计划件数</th>
                                    <th>计划总毛重（KG）</th>
                                    <th>计划总体积（CBM）</th>
                                    <th>预计提货时间</th>
                                </tr>
                            </thead>
                            <tbody>
                               {tr}
                            </tbody>
                        </table>
                        """
        df_html = ""
        if item_df.height > 0:
            tr = ''
            for r in item_df.iter_rows():
                tr += f"""
                    <tr>
                    <td>{r[0]}</td>
                    <td>{r[1]}</td>
                    <td>{r[2]}</td>
                    <td>{r[3]}</td>
                    <td>{r[4]}</td>
                </tr>
                    """
            # html模板
            df_html = f"""
                    <table  border=1>
                        <thead>
                            <tr>
                                <th>箱单号</th>
                                <th>合同号</th>
                                <th>电池货物明细描述</th>
                                <th>子项电池编码信息</th>
                                <th>CertificateCode</th>
                            </tr>
                        </thead>
                        <tbody>
                           {tr}
                        </tbody>
                    </table>
                    """
        # 获取pdf文件并打包
        nolist = []
        mail_Attachments = []
        if df_CertificateCode.height > 0:
            pdfzip = os.path.join(util.base_path, 'pdf_zip')
            create_folder_if_not_exists(pdfzip)
            nolist, mail_Attachments = create_zip_file(df_CertificateCode, util.pdf_path, pdfzip)
        create_email_template(util.recipient_email, box_html, df_html, mail_Attachments, excel_file_path, subject)

        # 转换为JSON字符串
        if len(nolist) == 0:
            nolist.append({
                "Item": '',
                "CertificateCode": ''
            })
        json_data = json.dumps(nolist)
        return json_data


    except Exception as e:
        return str(e)


def create_tem(cons, template_df, max_date):
    # 启动 Excel 应用
    app = xw.App(visible=False)
    template_wb = app.books.open(util.BOOKING_FORM_template_path)
    template_sheet = template_wb.sheets['模板收敛-AIR']
    for r in template_df.iter_rows():
        # 创建一个新的工作薄
        new_wb = xw.Book()
        template_sheet.api.Copy(Before=new_wb.sheets[0].api)
        new_wb.sheets[1].delete()  # 删除默认的工作表
        sht = new_wb.sheets[0]
        # 向单元格写入数据
        sht.range('A2').value = f"{r[0]}\r\n{r[1]}"  # Shipper+Shipper Address
        sht.range('A10').value = r[2]  # 海关申报收货人信息
        sht.range('K27').value = r[3]  # 最终目的地
        sht.range('K25').value = r[4]  # 目的地ID
        sht.range('AB23').value = r[5]  # 贸易术语
        sht.range('A29').value = r[6]  # 箱单号
        sht.range('G29').value = r[8]  # 计划件数
        sht.range('X29').value = r[9]  # 计划总毛重（KG）
        sht.range('AE29').value = r[7]  # 计划总体积（CBM）
        sht.range('X38').value = max_date  # CPD+1天(取最大CPD的日期+1天)
        sht.range('AG20').value = r[10]  # 如果贸易术语为FCA，就赋值√，否则就为空
        # 保存新的工作薄
        excel_name = ""
        for co in cons:
            excel_name = excel_name + co + " & "
        excel_name = excel_name[:-3]
        create_folder_if_not_exists(util.excel_path)
        excel_file_path = os.path.join(util.excel_path, f"BOOKING FORM-{excel_name}.xlsx")
        new_wb.save(excel_file_path)
        new_wb.close()
    template_wb.close()
    app.quit()
    return excel_file_path, excel_name


def fun_air(datapath, jsonstring):
    df = pl.DataFrame()
    air_df = util.config_df['air']
    box = air_df.filter(pl.col("原字段名") == "箱单号")["现字段名"].item(0)
    stt = air_df.filter(pl.col("原字段名") == "贸易术语")["现字段名"].item(0)
    dangerous = air_df.filter(pl.col("原字段名") == "危险品标识")["现字段名"].item(0)
    con = air_df.filter(pl.col("原字段名") == "合同号")["现字段名"].item(0)
    cbm = air_df.filter(pl.col("原字段名") == "计划总体积(CBM)")["现字段名"].item(0)
    item = air_df.filter(pl.col("原字段名") == "危险品货物明细描述")["现字段名"].item(0)
    sub_item = air_df.filter(pl.col("原字段名") == "子项危险品信息")["现字段名"].item(0)
    qty = air_df.filter(pl.col("原字段名") == "计划件数")["现字段名"].item(0)
    gw = air_df.filter(pl.col("原字段名") == "计划总毛重(KG)")["现字段名"].item(0)
    cpd = air_df.filter(pl.col("原字段名") == "CPD")["现字段名"].item(0)
    dest_id = air_df.filter(pl.col("原字段名") == "目的地ID")["现字段名"].item(0)
    dest = air_df.filter(pl.col("原字段名") == "最终目的地")["现字段名"].item(0)
    shipper = air_df.filter(pl.col("原字段名") == "Shipper")["现字段名"].item(0)
    shipper_address = air_df.filter(pl.col("原字段名") == "Shipper Address")["现字段名"].item(0)
    customs_info = air_df.filter(pl.col("原字段名") == "海关申报收货人信息")["现字段名"].item(0)
    if jsonstring == '无':
        df = (pl.read_excel(datapath, sheet_name=air_df["表名"].head(1).item(),
                            read_options={
                                'use_columns': air_df["现字段名"].to_list(),
                            },
                            # schema_overrides={"CPD": pl.Date},  # 覆盖默认的列类型
                            engine="calamine")
              .rename(
            {box: 'box', stt: 'stt', dangerous: 'dangerous', con: 'con', cbm: 'cbm',
             item: 'item', sub_item: 'sub_item', qty: 'qty', gw: 'gw',
             cpd: 'cpd', dest_id: 'dest_id', dest: 'dest', shipper: 'shipper',
             shipper_address: 'shipper_address', customs_info: 'customs_info'})  # 重命名列名
              .with_columns(pl.col('cpd').cast(pl.Date))
              .with_columns((pl.col("cpd") + pl.duration(days=1)).alias("cpd_1"))
              .fill_null('')

              )
    else:
        json_object = json.loads(jsonstring)
        df = (
            pl.DataFrame(json_object)
            .rename(
                {box: 'box', stt: 'stt', dangerous: 'dangerous', con: 'con', cbm: 'cbm',
                 item: 'item', sub_item: 'sub_item', qty: 'qty', gw: 'gw',
                 cpd: 'cpd', dest_id: 'dest_id', dest: 'dest', shipper: 'shipper',
                 shipper_address: 'shipper_address', customs_info: 'customs_info'})  # 重命名列名
            .with_columns(pl.col('cpd').cast(pl.Date))
            .with_columns((pl.col("cpd") + pl.duration(days=1)).alias("cpd_1"))
            .fill_null('')
        )
    stt = df['stt'][0]
    dest_id = df['dest_id'][0]
    dest = df['dest'][0]
    max_date = df['cpd_1'].max()
    cons = df['con'].unique().to_list()

    box_df = df.select(
        pl.col('box', 'con', 'stt', 'dangerous', 'qty', 'gw', 'cbm', 'cpd_1')
    )
    summary_row = pl.DataFrame({
        "box": [""],
        "con": [""],
        "stt": [""],
        "dangerous": ["总计："],
        "qty": [box_df["qty"].sum().__round__(2)],
        "gw": [box_df["gw"].sum().__round__(2)],
        "cbm": [box_df["cbm"].sum().__round__(2)],
        "cpd_1": ['']

    })
    # 将 cpd 列转换为字符串类型
    box_df = box_df.with_columns(pl.col("cpd_1").cast(pl.Utf8))
    summary_row = summary_row.with_columns(pl.col("cpd_1").cast(pl.Utf8))
    box_df = pl.concat(
        [box_df, summary_row],
        how='vertical'
    )

    # 提取item
    item_df = (
        df.select(pl.col('box', 'con', 'item', 'sub_item'))
        .filter(pl.col('item') != '')
        .with_columns(
            pl.col('item').str.replace_all(r',.*?/', '/').alias('item'),
            pl.col('sub_item').str.replace_all(r"90.*?;", '').alias('sub_item')
        )
        .with_columns(
            pl.col('item').str.split('/').alias('item'),
            pl.col('sub_item').str.split('/').alias('sub_item')
        )
        .explode(['item', 'sub_item'])
        .filter(pl.col('item') != '')
    )

    template_df = (
        df.group_by(['shipper', 'shipper_address', 'customs_info', 'dest', 'dest_id', 'stt'])
        .agg(
            pl.col('box').str.concat('\r\n').alias('box'),
            pl.col('cbm').sum().alias('cbm'),
            pl.col('qty').sum().alias('qty'),
            pl.col('gw').sum().alias('gw'),
        )
        .with_columns(
            pl.when(pl.col('stt') == 'FCA')
            .then(pl.lit('√'))
            .otherwise(pl.lit(''))
            .alias('fca')
        )

    )
    excel_file_path, con_name = create_tem(cons, template_df, max_date)
    subject = f"【请释放SO】{stt} {dest_id} TO {dest} -- {con_name}"

    json_data = create_oneEmail(item_df, excel_file_path, box_df, subject)
    return json_data
